package com.edu.zm;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class OperaDB {
	/**
	 * 老师管理系统对学生数据库的添加操作
	 * 
	 * @return 受影响的行数
	 */
	public static int insert() {
		int i = 0;
		boolean isCon = true;
		while (isCon) {
			Scanner sc = new Scanner(System.in);
			// 存放添加的数据的集合
			List list = new ArrayList();
			System.out.println("请输入学生编号：");
			list.add(sc.nextInt());
			System.out.println("请输入学生账号密码：");
			list.add(sc.nextInt());
			System.out.println("请输入学生姓名：");
			list.add(sc.next());
			System.out.println("请输入学生性别：");
			list.add(sc.next());
			System.out.println("请输入学生年龄：");
			list.add(sc.nextInt());
			System.out.println("请输入学生手机号码：");
			list.add(sc.nextInt());
			System.out.println("请输入学生家庭住址：");
			list.add(sc.next());
			Object[] obj = list.toArray();
			System.out.println("正在添加请稍后...");

			// 获取数据库连接驱动对象
			Connection conn = Dbconnect.getConn();
			String sql = "insert into educational (student_id,student_pasd,student_name,student_sex,student_age,student_phnum,student_loc) values(?,?,?,?,?,?,?)";

			try {
				// 获得数据库操作对象
				PreparedStatement pst = conn.prepareStatement(sql);
				for (int k = 1; k <= obj.length; k++) {
					pst.setObject(k, obj[k - 1]);
				}
				// 将结果写入数据库
				pst.executeUpdate();
				i++;
				OperaDB.stuMainInfo();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.getMessage();
			}
			System.out.println("是否继续添加【no/yes】");
			String isCon1 = sc.next();
			if (isCon1.equals("no")) {
				isCon = false;
			}
		}

		// 返回受影响的行数
		return i;
	}

	public static void queryDb() {

		// 声明查询 语句
		String sqlStr = null;
		// 声明查询关键字
		String queryStr;
		ResultSet rs;
		Scanner sc;
		// 查询开始条件
		boolean isCon2 = true;
		boolean isCon = true;
		while (isCon) {
			sc = new Scanner(System.in);

			try {
				// 内存循环判断数据库中是否有此数据
				while (isCon2) {
					System.out.println("请选择相应选择条件进行筛选");
					System.out.println("1.姓名");
					System.out.println("2.性别");
					System.out.println("3.学号");
					System.out.println("4.电话");
					int isChoice = sc.nextInt();
					switch (isChoice) {
					case 1:
						System.out.println("输入姓名进行模糊查询");
						queryStr = sc.next();
						sqlStr = "select * from educational where student_name like '%" + queryStr + "%'";
						break;
					case 2:
						System.out.println("输入性别进行模糊查询");
						queryStr = sc.next();
						sqlStr = "select * from educational where student_sex like '%" + queryStr + "%'";
						break;
					case 3:
						System.out.println("输入学号进行模糊查询");
						queryStr = sc.next();
						sqlStr = "select * from educational where student_id like '%" + queryStr + "%'";
						break;
					case 4:
						System.out.println("输入电话进行模糊查询");
						queryStr = sc.next();
						sqlStr = "select * from educational where student_phnum like '%" + queryStr + "%'";
						break;
					}

					System.out.println("正在查询请稍后...");
					// 将查询到的结果集写入educational对象集合中
					List<Educational> list = OperaDB.getList(sqlStr, Educational.class);
					if (list.size() == 0) {
						System.out.println("查询的结果不存在，请核对后查询");
					} else {
						for (Educational edu : list) {
							System.out.println(edu);
						}
						break;
					}
				}

				System.out.println("是否继续查询【no/yes】");
				String isCon1 = sc.next();
				if (isCon1.equals("no")) {
					isCon = false;
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * @param clazz
	 * @return返回一个学生信息表信息的对象集合
	 */
	public static <T> List<T> getList(String strSql, Class<T> clazz) {
		List<T> list = null;

		// 返回数据库查询对象
		Statement st;
		try {
			st = Dbconnect.getConn().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			// 查询教务系统表中所有的学生信息
			ResultSet rs = st.executeQuery(strSql);
			list = OperaDB.getListObject(rs, clazz);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;

	}

	public static void upadate() {
		int i = 0;
		boolean isCon = true;
		// 修改前查询所有学生信息
		OperaDB.stuMainInfo();
		while (isCon) {
			Scanner sc = new Scanner(System.in);
			// 选择要进行操作的行
			System.out.println("请输入要修改的学生id：");
			String stu_id = sc.next();
			String sqlStr="select * from educational where student_id ="+stu_id;
			//判断数据库中是否存在此数据
			if (Dbconnect.simplePlant(OperaDB.class).getList(sqlStr, Educational.class).size()>0) {
				// 存放添加的数据的集合
				List list = new ArrayList();
				System.out.println("请输入新的学生编号：");
				list.add(sc.nextInt());
				System.out.println("请输入新的学生账号密码：");
				list.add(sc.nextInt());
				System.out.println("请输入新的学生姓名：");
				list.add(sc.next());
				System.out.println("请输入新的学生性别：");
				list.add(sc.next());
				System.out.println("请输入新的学生年龄：");
				list.add(sc.nextInt());
				System.out.println("请输入新的学生手机号码：");
				list.add(sc.nextInt());
				System.out.println("请输入新的学生家庭住址：");
				list.add(sc.next());
				Object[] obj = list.toArray();
				System.out.println("正在修改请稍后...");
				String sql = "update educational set student_id=?,student_pasd=?,student_name=?,student_sex=?,student_age=?,student_phnum=?,student_loc=? where student_id="
						+ stu_id;
				// 获得数据库连接对象
				try {
					PreparedStatement pst = Dbconnect.getConn().prepareStatement(sql);
					// 将修改的结果存入到结果集中
					for (int k = 1; k <= obj.length; k++) {
						pst.setObject(k, obj[k - 1]);
					}
					pst.executeUpdate();
					i++;
					System.out.println("*****************修改后的的信息如下*******************");

					OperaDB.stuMainInfo();

					System.out.println("是否继续修改【no/yes】");
					String isCon1 = sc.next();
					if (isCon1.equals("no")) {
						isCon = false;
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				//未找到相应可修改学号的情况下
			}else if(Dbconnect.simplePlant(OperaDB.class).getList(sqlStr, Educational.class).size()==0){
				System.out.println("未找到相应的学号,是否继续修改【1.是/2.否】");
				if(sc.nextInt()==1) {
				isCon=true;
				}
				isCon=false;
			}
		}
		System.out.println("已修改" + i + "行");
	}

	/**
	 * 加载学生信息功能
	 */
	public static void loadStuInfo() {
		boolean isCon = true;
		Scanner sc = new Scanner(System.in);
		while (isCon) {
			System.out.println("加载学生信息中...");
			int i = stuMainInfo();
			System.out.println("共有" + i + "条信息");
			System.out.println("是否继续加加载【no/yes】");
			String isCon1 = sc.next();
			if (isCon1.equals("no")) {
				isCon = false;
			}
		}
	}

	public static void deleteStuInfor() {
		int i = 0;
		boolean isCon = true;
		while (isCon) {
			try {
				Scanner sc = new Scanner(System.in);
				OperaDB.stuMainInfo();
				System.out.println("---------------------请选择要删除学生信息的学号：------------------");
				// 控制台录入要接收的学号
				String choice = sc.next();

				PreparedStatement pst = Dbconnect.getConn()
						.prepareStatement("delete educational where student_id=" + choice);
				// 执行删除
				pst.executeQuery();
				i++;
				System.out.println("删除后的信息表为");
				OperaDB.stuMainInfo();
				System.out.println("是否继续删除【no/yes】");
				String isCon1 = sc.next();
				if (isCon1.equals("no")) {
					isCon = false;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		System.out.println("已删除" + i + "条信息！");
	}

	/**
	 * @param rs
	 * @param clazz
	 * @return每次操作后显示全部类型信息的语句
	 */
	public static void saveStuInfo() {
		System.out.println("是否保存【1.yse】/【2.no】");
		Scanner sc = new Scanner(System.in);
		if (sc.nextInt() == 1) {
			System.out.println("保存成功！");
		} else {
			System.out.println("退出保存！");
		}
	}

	public static int stuMainInfo() {
		int i = 0;
		List list1 = OperaDB.getList("select * from educational order by student_id desc", Educational.class);
		for (Object object : list1) {
			System.out.print(object);
			System.out.println();
			i++;
		}
		return i;
	}

	public static <T> List<T> getListObject(ResultSet rs, Class<T> clazz) {
		List<T> list = new ArrayList();
		if (rs != null) {
			try {
				// 因为已经遍历过一次结果，所有再次遍历之前，需要将光标移动到最前位置（第一行之前）
				rs.beforeFirst();
				// 获取结果集中的所有的列的信息
				ResultSetMetaData rsmd = rs.getMetaData();
				// 获取结果集中列的个数,等于每行中字段的个数
				int colCount = rsmd.getColumnCount();
				while (rs.next()) {
					// 添加每行信息保存的实力类对象，类中的，每个属性保存每列的信息
					T tClass = clazz.newInstance();
					// 循环每行找那个每个字段的值
					for (int i = 1; i <= colCount; i++) {
						// 获取当前列或者当前属性的字段名
						String strKey = rsmd.getColumnName(i);
						// 获取当前字段对应类中的属性
						Field field = clazz.getDeclaredField(strKey.toLowerCase());
						// 设置当前属性可操作性为true
						field.setAccessible(true);
						// 给每个字段类型属性赋值，期间将jdbc类型转为java类型再赋值
						field.set(tClass, Dbconnect.changeProType(rs.getObject(strKey), field.getType().getName()));
					}
					list.add(tClass);
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchFieldException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return list;

	}

}
